''' <summary>
''' Class for all classes to read MDB (Access) data (or other OLEDB datasources like DB and DBF)
''' </summary>
Public Class clsDataOleDB
    Inherits clsData
    Implements IDisposable

    Private mConnection As Data.OleDb.OleDbConnection
    Private mDataReader As Data.OleDb.OleDbDataReader
    Private mNumRecords As Integer

    Private mIsExcel As Boolean = False

    ''' <summary>
    ''' Instantiate clsData, assign filename, open file, set filelength and fieldnames
    ''' </summary>
    ''' <param name="_Filename">Name of text file to read</param>
    Public Overrides Sub Initialize(ByVal _Filename As String)
        MyBase.Initialize(_Filename)
        mDataType = enumDataType.OleDB

        With New Data.OleDb.OleDbConnectionStringBuilder
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            If mFilename.EndsWith(".DB", StringComparison.OrdinalIgnoreCase) Then
                mFilename = mFilename.ToUpper
                .Item("Extended Properties") = "Paradox 7.x"
                .DataSource = IO.Path.GetDirectoryName(mFilename) & "\"
            ElseIf mFilename.EndsWith(".DBF", StringComparison.OrdinalIgnoreCase) Then
                .Item("Extended Properties") = "dBASE IV"
                .DataSource = IO.Path.GetDirectoryName(mFilename) & "\"
            ElseIf mFilename.EndsWith(".MDB", StringComparison.OrdinalIgnoreCase) Then
                .DataSource = mFilename
            ElseIf mFilename.EndsWith(".XLS", StringComparison.OrdinalIgnoreCase) Then
                .Item("Extended Properties") = "Excel 8.0;HDR=YES;IMEX=1;MAXSCANROWS=15;READONLY=FALSE"
                .DataSource = mFilename '(note: table names should be formatted like [Sheet1$])
                mIsExcel = True
            ElseIf mFilename.EndsWith(".XLSX", StringComparison.OrdinalIgnoreCase) Or mFilename.EndsWith(".XLSM", StringComparison.OrdinalIgnoreCase) Then
                .Provider = "Microsoft.ACE.OLEDB.12.0"
                .Item("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1;MAXSCANROWS=15;READONLY=FALSE"
                .DataSource = mFilename '(note: table names should be formatted like [Sheet1$])
                mIsExcel = True
            End If
            mConnection = New OleDb.OleDbConnection(.ConnectionString)
        End With

        If mFilename.EndsWith(".MDB", StringComparison.OrdinalIgnoreCase) Or mFilename.EndsWith(".XLS", StringComparison.OrdinalIgnoreCase) Or mFilename.EndsWith(".XLSX", StringComparison.OrdinalIgnoreCase) Or mFilename.EndsWith(".XLSM", StringComparison.OrdinalIgnoreCase) Then
            mConnection.Open()
            Dim SchemaTable As DataTable = mConnection.GetSchema("Tables")
            For Each row As DataRow In SchemaTable.Rows
                Dim TableName As String = RemoveBrackets(row.Item("Table_Name"))
                If mIsExcel Then TableName = TableName.Replace("$", "")
                Dim TableType As String = row.Item("Table_Type")
                'see if is a system table
                If (TableType.ToUpper = "TABLE" Or TableType.ToUpper = "BASE TABLE") And Not mTableNames.Contains(TableName) Then
                    mTableNames.Add(MyBase.RemoveBrackets(TableName))
                End If
            Next
            mConnection.Close()
        Else
            ActiveTable = IO.Path.GetFileNameWithoutExtension(mFilename) 'to force it to get the fieldnames
            mTableNames.Add(RemoveBrackets(mActiveTable))
        End If
    End Sub

    ''' <summary>
    ''' Read next line from file into memory, storing into mItems array
    ''' Also save mLastLineRead for error reporting
    ''' </summary>
    ''' <returns>True if successful, false if eof reached and no more data</returns>
    Public Overrides Function Read() As Boolean
        Try
            If Not MyBase.Read() Then Return False
            mItems.Clear()
            With mDataReader
                If .Read Then
                    For i As Integer = 0 To .FieldCount - 1
                        If .GetFieldType(i) Is GetType(Date) And Not .IsDBNull(i) Then
                            If .GetName(i).ToUpper = "DATE" Then
                                mItems.Add(.GetDateTime(i).ToString(MyDateFormat))
                            ElseIf .GetName(i).ToUpper = "TIME" Then
                                mItems.Add(.GetDateTime(i).ToString("t"))
                            Else
                                mItems.Add(RoundDateTime(.GetDateTime(i)).ToString(MyDateTimeFormat))
                            End If
                        Else
                            If .IsDBNull(i) Then mItems.Add("") Else mItems.Add(.GetValue(i).ToString.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbTab, ""))
                        End If
                    Next
                    Return True
                Else
                    mConnection.Close()
                    Return False
                End If
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        End Try
    End Function

    Public Overrides Sub Rewind()
        MyBase.Rewind()
        ActiveTable = MyBase.ActiveTable 'force datareader to be recreated
    End Sub

    ''' <summary>
    ''' Set or get the currently selected table
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>Set the fieldnames, get the number or records and leave the connection open to start reading</remarks>
    Public Overrides Property ActiveTable() As String
        Get
            Return MyBase.ActiveTable
        End Get
        Set(ByVal value As String)
            If mActiveTable = value Then Exit Property 'so don't set it multiple times which would be slow

            If mIsExcel Then
                value = String.Format("[{0}$]", value.Replace("'", "").Replace("$", ""))
            Else
                value = String.Format("[{0}]", value)
            End If

            MyBase.ActiveTable = value

            If mConnection.State = ConnectionState.Open Then mDataReader.Close()
            If mConnection.State = ConnectionState.Closed Then mConnection.Open()

            mFieldNames.Clear()
            Dim cmd0 As New Data.OleDb.OleDbCommand(String.Format("SELECT * FROM {0}", value), mConnection)
            Dim dr As Data.OleDb.OleDbDataReader = cmd0.ExecuteReader(CommandBehavior.SchemaOnly)
            If dr Is Nothing Then Exit Property
            For i As Integer = 0 To dr.FieldCount - 1
                mFieldNames.Add(dr.GetName(i))
            Next
            dr.Close()

            Dim cmd1 As New Data.OleDb.OleDbCommand(String.Format("SELECT COUNT(*) FROM {0}", value), mConnection)
            mNumRecords = cmd1.ExecuteScalar

            mStationIDs.Clear()
            If ContainsFieldName("STATION_ID") Then
                Dim cmd As New Data.OleDb.OleDbCommand(String.Format("SELECT DISTINCT STATION_ID FROM {0}", value), mConnection)
                dr = cmd.ExecuteReader()
                While dr IsNot Nothing AndAlso dr.Read
                    If Not dr.IsDBNull(0) Then mStationIDs.Add(CStr(dr.GetValue(0)))
                End While
                dr.Close()
                mStationIDs.Sort()
                If mStationIDs.Count > 0 Then mActiveStationID = mStationIDs(0)
            End If

            mPCodes.Clear()
            If ContainsFieldName("PCODE") Then
                Dim cmd As New Data.OleDb.OleDbCommand(String.Format("SELECT DISTINCT PCODE FROM {0}", value), mConnection)
                dr = cmd.ExecuteReader()
                While dr IsNot Nothing AndAlso dr.Read
                    If Not dr.IsDBNull(0) Then mPCodes.Add(dr.GetString(0))
                End While
                dr.Close()
                mPCodes.Sort()
            End If

            Dim cmd2 As New Data.OleDb.OleDbCommand(String.Format("SELECT * FROM {0}", value), mConnection)
            mDataReader = cmd2.ExecuteReader()

        End Set
    End Property

    Public Overrides Property ActiveStationID() As String
        Get
            Return MyBase.ActiveStationID
        End Get
        Set(ByVal value As String)
            MyBase.ActiveStationID = value
            If Not mDataReader.IsClosed Then mDataReader.Close()
            If mConnection.State = ConnectionState.Closed Then mConnection.Open()

            Dim cmd1 As New Data.OleDb.OleDbCommand(String.Format("SELECT COUNT(*) FROM {0} WHERE Station_ID='{1}'", mActiveTable, value), mConnection)
            mNumRecords = cmd1.ExecuteScalar

            'note: mindate/maxdate is based on station id only (not pcode)
            cmd1 = New Data.OleDb.OleDbCommand(String.Format("SELECT MIN(Date_Time),MAX(Date_Time) FROM {0} WHERE Station_ID='{1}' GROUP BY Station_ID", mActiveTable, value), mConnection)
            Dim dr As Data.Common.DbDataReader = cmd1.ExecuteReader
            If dr IsNot Nothing And dr.Read Then
                mMinDate = TestNull(dr.GetValue(0), CDate("1/1/1901"))
                mMaxDate = TestNull(dr.GetValue(1), CDate("1/1/1901"))
            End If
            dr.Close()

            cmd1 = New Data.OleDb.OleDbCommand(String.Format("SELECT DISTINCT PCODE FROM {0} WHERE Station_ID='{1}'", mActiveTable, value), mConnection)
            dr = cmd1.ExecuteReader
            mPCodes.Clear()
            While dr IsNot Nothing And dr.Read
                mPCodes.Add(dr.GetValue(0))
            End While
            dr.Close()
            cmd1.Dispose()

            Dim cmd2 As New Data.OleDb.OleDbCommand(String.Format("SELECT * FROM {0} WHERE Station_ID='{1}'", mActiveTable, value), mConnection)
            mDataReader = cmd2.ExecuteReader() 'it is read to READ now
        End Set
    End Property

    ''' <summary>
    ''' Get data table containing time series data for a specified station id and pcode
    ''' Table will contain date-time field and result field
    ''' </summary>
    Public Overloads Function GetDataTable(ByVal StationID As String, ByVal PCode As String) As DataTable
        If Not mStationIDs.Contains(StationID) Then
            Throw New ApplicationException("OleDB table does not contain the station: " & StationID)
            Return Nothing
        End If
        Dim itemNum As Integer = -1
        If ContainsFieldName(PCode) Then
            itemNum = FieldIndex(PCode)
        Else
            'some tables are WRDB format and will contain PCode and Result fields; handle that
            If Not (ContainsFieldName("PCODE") And ContainsFieldName("RESULT")) Then
                Throw New ApplicationException("OleDB table does not contain the column: " & PCode)
                Return Nothing
            End If
        End If
        Dim dtNum As Integer = -1
        If ContainsFieldName("DATE_TIME") Then
            dtNum = FieldIndex("DATE_TIME")
        Else
            Throw New ApplicationException("OleDB table does not contain the column: DATE_TIME")
            Return Nothing
        End If
        Dim dt As DataTable = MyBase.SetupDataTable(enumDataTableColumns.Station_Result)
        Dim cmd As New Data.OleDb.OleDbCommand()
        cmd.Connection = mConnection
        If ContainsFieldName("PCODE") And ContainsFieldName("RESULT") Then
            cmd.CommandText = String.Format("SELECT DATE_TIME,RESULT FROM {1} WHERE STATION_ID='{2}' AND PCODE='{3}' ORDER BY DATE_TIME", PCode, MyBase.ActiveTable, StationID, PCode)
        Else
            cmd.CommandText = String.Format("SELECT DATE_TIME,{0} AS RESULT FROM {1} WHERE STATION_ID='{2}' ORDER BY DATE_TIME", PCode, MyBase.ActiveTable, StationID)
        End If

        Dim dr As OleDb.OleDbDataReader = cmd.ExecuteReader()
        While dr IsNot Nothing And dr.Read
            dt.Rows.Add(dr.GetValue(0), dr.GetValue(1))
        End While
        dr.Close()
        Return dt
    End Function

    ''' <summary>
    ''' Determine percent of file that has been read
    ''' </summary>
    ''' <returns>Percent of file read</returns>
    Public Overrides Function PercentComplete() As Double
        Return mRecordNum * 100.0 / mNumRecords
    End Function

    Public Overrides Sub Close()
        MyBase.Close()
        mDataReader.Close()
        mConnection.Close()
    End Sub

    Private disposed As Boolean = False

    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposed Then
            If disposing Then
                mDataReader = Nothing
                mConnection.Dispose()
                mConnection = Nothing
            End If
        End If
        Me.disposed = True
    End Sub

End Class
